package com.yupi.springbootinit.service.impl;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.rholder.retry.*;
import com.yupi.springbootinit.Listener.AIListener;
import com.yupi.springbootinit.common.ErrorCode;
import com.yupi.springbootinit.constant.CommonConstant;
import com.yupi.springbootinit.exception.BusinessException;
import com.yupi.springbootinit.exception.ThrowUtils;
import com.yupi.springbootinit.manager.AiManager;
import com.yupi.springbootinit.model.dto.chart.ChartQueryRequest;
import com.yupi.springbootinit.model.entity.Chart;
import com.yupi.springbootinit.model.entity.User;
import com.yupi.springbootinit.model.enums.ResultEnum;
import com.yupi.springbootinit.model.vo.BiResponse;
import com.yupi.springbootinit.model.vo.ChartVO;
import com.yupi.springbootinit.service.ChartService;
import com.yupi.springbootinit.mapper.ChartMapper;
import com.yupi.springbootinit.utils.ExcelUtils;
import com.yupi.springbootinit.utils.InvalidJsonUtils;
import com.yupi.springbootinit.utils.SqlUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;
import java.util.stream.Collectors;

/**
 * @author cj
 * @description 针对表【chart(图表信息表)】的数据库操作Service实现
 * @createDate 2024-06-27 10:07:34
 */
@Service
public class ChartServiceImpl extends ServiceImpl<ChartMapper, Chart>
        implements ChartService {

    @Resource
    private ChartMapper chartMapper;

    @Resource
    private AiManager aiManager;

    @Resource
    private ThreadPoolExecutor threadPoolExecutor;


    @Override
    public Page<ChartVO> pageChart(Long current, Long size, ChartQueryRequest chartQueryRequest) {

        QueryWrapper<Chart> queryWrapper = new QueryWrapper<>();
        if (chartQueryRequest == null) {
            return null;
        }
        Long id = chartQueryRequest.getId();
        String name = chartQueryRequest.getName();
        String goal = chartQueryRequest.getGoal();
        String chartType = chartQueryRequest.getChartType();
        Long userId = chartQueryRequest.getUserId();
        String sortField = chartQueryRequest.getSortField();
        String sortOrder = chartQueryRequest.getSortOrder();

        queryWrapper.eq(id != null && id > 0, "id", id);
        queryWrapper.like(StringUtils.isNotBlank(name), "name", name);
        queryWrapper.eq(StringUtils.isNotBlank(goal), "goal", goal);
        queryWrapper.eq(StringUtils.isNotBlank(chartType), "chart_type", chartType);
        queryWrapper.eq(ObjectUtils.isNotEmpty(userId), "userId", userId);

        queryWrapper.eq("isDelete", false);
        queryWrapper.orderBy(SqlUtils.validSortField(sortField), sortOrder.equals(CommonConstant.SORT_ORDER_ASC),
                sortField);
        Page<Chart> pageChart = this.page(new Page<>(current, size), queryWrapper);
        List<ChartVO> chartVOList = pageChart.getRecords().stream().map((chart) -> {
            ChartVO chartVO = new ChartVO();
            BeanUtils.copyProperties(chart, chartVO);
            String chartData = String.valueOf(chartMapper.selectChart(String.valueOf(chart.getId())));
            chartVO.setChartData(chartData);
            return chartVO;
        }).collect(Collectors.toList());
        Page<ChartVO> pageChartVO = new Page<>();
        pageChartVO.setRecords(chartVOList);
        return pageChartVO;
    }



    @Override
    public BiResponse SyncAISync(MultipartFile multipartFile, String name, String goal, String chartType, User loginUser) {

        final String prompt = "你是一个数据分析师和前端开发专家，接下来我会按照以下固定格式给你提供内容：\n" +
                "分析需求：\n" +
                "{数据分析的需求或者目标}\n" +
                "原始数据：\n" +
                "{csv格式的原始数据，用,作为分隔符}\n" +
                "请根据这两部分内容，按照以下指定格式生成回答内容（此外不要输出任何多余的开头、结尾、注释）\n" +
                "【【【【【\n" +
                "{前端 Echarts V5的option配置对象的json代码（只生成option里的代码，不要有option的声明,只能使用双引号”“，代码要简洁规范准确），" +
                "合理地将数据进行可视化，不要生成任何多余的内容，比如注释}\n" +
                "【【【【【\n" +
                "{明确的数据分析结论、越详细越好，不要生成多余的注释}";

        StringBuilder userInput = new StringBuilder();
        userInput.append("分析需求：").append("\n");
        //拼接分析目标
        String userGoal = goal;
        if (StringUtils.isNotBlank(chartType)) {
            userGoal += "请使用" + chartType;
        }

        userInput.append(userGoal).append("\n");
        userInput.append("原始数据：").append("\n");
        String resultDate = ExcelUtils.excelToCsv(multipartFile);
        userInput.append(resultDate).append("\n");
        System.out.println(userInput);
        BiResponse biResponse = new BiResponse();
        // 调用AI接口
        Callable<Boolean> callable = () -> {
            String response = aiManager.doChat(prompt , userInput.toString());
            System.out.println(response);
            String[] split = response.split("【【【【【");

            if (split.length < 3) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "AI生成失败，请检查输入数据");
            }
            String genChart = split[1].trim();
            String genResult = split[2].trim();
            // 保存到数据库
            Chart chart = new Chart();
            chart.setName(name);
            chart.setGoal(goal);
            //chart.setChartData(resultDate);
            chart.setChartType(chartType);
            chart.setGenChart(genChart);
            chart.setGenResult(genResult);
            chart.setUserId(loginUser.getId());
            chart.setStatus(ResultEnum.SUCCEED.getDes());
            //校验Json格式
            if (!InvalidJsonUtils.isValidString(genChart)){
                chart.setStatus(ResultEnum.FAILED.getDes());
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "Json格式错误" );
            }
            boolean save = this.save(chart);
            createTable(chart.getId(), multipartFile);

            if (!save){
                handleChartUpdateError(chart.getId(),"图表保存失败");
            }
            biResponse.setGenChart(genChart);
            biResponse.setGenResult(genResult);
            biResponse.setChartId(chart.getId());
            return true;
        };
        try {
            retryer.call(callable); // 执行
        } catch (RetryException | ExecutionException e) { // 重试次数超过阈值或被强制中断
            e.printStackTrace();
        }
        return biResponse;
    }

    @Override
    public BiResponse AsyncAISync(MultipartFile multipartFile, String name, String goal, String chartType, User loginUser) {

        final String prompt = "你是一个数据分析师和前端开发专家，接下来我会按照以下固定格式给你提供内容：\n" +
                "分析需求：\n" +
                "{数据分析的需求或者目标}\n" +
                "原始数据：\n" +
                "{csv格式的原始数据，用,作为分隔符}\n" +
                "请根据这两部分内容，按照以下指定格式生成回答内容（此外不要输出任何多余的开头、结尾、注释）\n" +
                "【【【【【\n" +
                "{前端 Echarts V5的option配置对象的json代码（只生成option里的代码，不要有option的声明,只能使用双引号”“，代码要简洁规范准确），" +
                "合理地将数据进行可视化，不要生成任何多余的内容，比如注释}\n" +
                "【【【【【\n" +
                "{明确的数据分析结论、越详细越好，不要生成多余的注释}";

        StringBuilder userInput = new StringBuilder();
        userInput.append("分析需求：").append("\n");
        //拼接分析目标
        String userGoal = goal;
        if (StringUtils.isNotBlank(chartType)) {
            userGoal = "请使用" + chartType + userGoal;
        }

        userInput.append(userGoal).append("\n");
        userInput.append("原始数据：").append("\n");
        String resultDate = ExcelUtils.excelToCsv(multipartFile);
        userInput.append(resultDate).append("\n");
        // 保存到数据库
        Chart chart = new Chart();
        chart.setName(name);
        chart.setGoal(goal);
        //chart.setChartData(resultDate);
        chart.setChartType(chartType);
        chart.setUserId(loginUser.getId());
        chart.setStatus(ResultEnum.WAIT.getDes());
        boolean save = this.save(chart);
        ThrowUtils.throwIf(!save, ErrorCode.SAVE_ERROR, "图表保存失败");
        //单独生成图表信息表
        createTable(chart.getId(), multipartFile);

        BiResponse biResponse = new BiResponse();
        biResponse.setChartId(chart.getId());
        // 异步调用AI接口
        try {
            CompletableFuture.runAsync(() -> {

                Chart updateChart = new Chart();
                updateChart.setId(chart.getId());
                updateChart.setStatus(ResultEnum.RUNNING.getDes());
                boolean b = this.updateById(updateChart);
                if (!b) {
                    handleChartUpdateError(chart.getId(), "更新图表执行中更新失败");
                    return;
                }

                // 调用AI接口
                Callable<Boolean> callable = () -> {
                    String response = aiManager.doChat(prompt, userInput.toString());
                    String[] split = response.split("【【【【【");
                    if (split.length < 3) {
                        handleChartUpdateError(chart.getId(), "AI生成失败，请检查输入数据");
                        throw new BusinessException(ErrorCode.PARAMS_ERROR, "AI生成失败，请检查输入数据" );
                    }
                    String genChart = split[1].trim();
                    System.out.println(genChart);
                    String genResult = split[2].trim();

                    Chart updateChartAI = new Chart();
                    updateChartAI.setId(chart.getId());
                    //校验Json格式
                    if (!InvalidJsonUtils.isValidString(genChart)) {
                        updateChart.setStatus(ResultEnum.FAILED.getDes());
                        throw new BusinessException(ErrorCode.PARAMS_ERROR, "Json格式错误" );
                    } else {
                        updateChart.setGenResult(genResult);
                        updateChart.setGenChart(genChart);
                        updateChart.setStatus(ResultEnum.SUCCEED.getDes());
                    }
                    boolean a = this.updateById(updateChart);
                    if (!a) {
                        handleChartUpdateError(chart.getId(), "更新图表完成状态更新失败");
                    }
                    return true;
                };
                try {
                    retryer.call(callable); // 执行
                } catch (RetryException | ExecutionException e) { // 重试次数超过阈值或被强制中断
                    e.printStackTrace();
                }
            }, threadPoolExecutor);
        } catch (Exception e) {
            log.error("请稍后再试", e);
        }

        return biResponse;
    }

    Retryer<Boolean> retryer = RetryerBuilder.<Boolean>newBuilder()
            .retryIfException()
            .retryIfResult(Boolean.FALSE::equals)
            .retryIfRuntimeException() // 发生运行时异常则重试
            .withWaitStrategy(WaitStrategies.incrementingWait(10, TimeUnit.SECONDS, 10, TimeUnit.SECONDS)) // 等待
            .withStopStrategy(StopStrategies.stopAfterAttempt(3)) // 允许执行3次（首次执行 + 最多重试3次）
            .withRetryListener(new AIListener())
            .build();


    private void handleChartUpdateError(long chartId, String execMessage) {
        Chart updateChartResult = new Chart();
        updateChartResult.setId(chartId);
        updateChartResult.setStatus(ResultEnum.FAILED.getDes());
        updateChartResult.setExecMessage(execMessage);
        boolean b = this.updateById(updateChartResult);
        if (!b) {
            log.error("更新图表状态失败" + chartId + " , " + execMessage);
        }
    }
    public void createTable(Long chartId, MultipartFile multipartFile) {
        String table_name = "chart_" + chartId;
        StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS " + table_name + " (");
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误");
            throw new RuntimeException(e);
        }
        //读取表头
        LinkedHashMap<Integer, String> headerMap = (LinkedHashMap) list.get(0);
        List<String> headerList = headerMap.values().stream().filter(ObjectUtils::isNotEmpty).collect(Collectors.toList());
        for (String header : headerList) {
            createTableSql.append(header).append(" VARCHAR(255), ");
        }
        createTableSql.setLength(createTableSql.length() - 2); // 移除最后一个逗号
        createTableSql.append(");");
        chartMapper.executeSQL(createTableSql.toString());
        // 生成插入数据的SQL
        for (int i = 1; i < list.size(); i++) {
            StringBuilder insertSql = new StringBuilder("INSERT INTO " + table_name + " VALUES (");
            LinkedHashMap<Integer, String> dataMap = (LinkedHashMap) list.get(i);
            List<String> dataList = dataMap.values().stream().filter(ObjectUtils::isNotEmpty).collect(Collectors.toList());
            for (String cell : dataList) {
                insertSql.append("'").append(cell).append("', ");
            }
            insertSql.setLength(insertSql.length() - 2); // 移除最后一个逗号
            insertSql.append(");");

            // 执行插入数据的SQL
            chartMapper.executeSQL(insertSql.toString());
        }
    }
}




